USE [Musicman]
GO
-- update of user_feedback
if exists(select id from sysobjects 
where name = 'TR_USERFEEDBACK_UPDATED' 
and objectproperty(id, 'ExecIsUpdateTrigger') = 1
and objectproperty(id, 'ExecIsAfterTrigger ') = 1)
begin
DROP TRIGGER [dbo].[TR_USERFEEDBACK_UPDATED] 
END 
GO


CREATE TRIGGER [dbo].[TR_USERFEEDBACK_UPDATED]
   ON  [dbo].[USERFEEDBACK]
   AFTER UPDATE
AS 
BEGIN
	-- inserted / deleted
	IF( SELECT COUNT(*) FROM inserted  ) = 1
	BEGIN
		update t1 
		set DATE = getDate()
		FROM USERFEEDBACK t1 INNER JOIN inserted t2 ON t1.ID = t2.ID
	END	
END
go
 
-- update of user_stats

if exists(select id from sysobjects 
where name = 'TR_USERSTATS_UPDATED' 
and objectproperty(id, 'ExecIsUpdateTrigger') = 1
and objectproperty(id, 'ExecIsAfterTrigger ') = 1)
begin
DROP TRIGGER [dbo].[TR_USERSTATS_UPDATED] 
END 
GO


CREATE TRIGGER [dbo].[TR_USERSTATS_UPDATED]
   ON  [dbo].[USERSTATS]
   AFTER UPDATE
AS 
BEGIN
	-- inserted / deleted
	IF( SELECT COUNT(*) FROM inserted  ) = 1
	BEGIN
		update t1 
		set LASTLOGGEDDATE = getDate()
		FROM USERSTATS t1 INNER JOIN inserted t2 ON t1.Personid = t2.Personid
	END	
END
go

-- triger not needed any more
if exists(select id from sysobjects 
where name = 'TR_USERFEEDBACK_UPDATED' 
and objectproperty(id, 'ExecIsUpdateTrigger') = 1
and objectproperty(id, 'ExecIsAfterTrigger ') = 1)
begin
DROP TRIGGER [dbo].[TR_USERFEEDBACK_UPDATED] 
END 
GO


USE MUSICMAN
GO 

if exists(select id from sysobjects 
where name = 'TR_USERFEEDBACK_UPDATED' 
and objectproperty(id, 'ExecIsUpdateTrigger') = 1
and objectproperty(id, 'ExecIsInsertTrigger') = 1
and objectproperty(id, 'ExecIsAfterTrigger ') = 1)
begin
DROP TRIGGER [dbo].[TR_USERFEEDBACK_UPDATED] 
END 
GO


CREATE TRIGGER [dbo].[TR_USERFEEDBACK_UPDATED]
   ON  [dbo].[USERFEEDBACK]
   AFTER UPDATE, INSERT
AS 
BEGIN
	-- inserted / deleted
	IF( SELECT COUNT(*) FROM inserted  ) = 1
	BEGIN
		declare @subject varchar(100)
		declare @feedback varchar(1024)
		declare @from varchar(200)
		declare @date varchar(10)
		select @subject = uf.subject, @feedback = uf.feedback, @date = convert(varchar(10), uf.date, 101), @from = (u.firstname + ' ' + u.lastname) 
			from musicman.dbo.userFeedback uf
			inner join musicman.dbo.users u on uf.personid = u.personid 
			inner join inserted up on up.ID = uf.ID

			declare @subject1 varchar(100)
			set @subject1 = 'Feedback updated ' + convert(varchar(50), newId())
			declare @body1 varchar(1000)
			set @body1 = 'Server :'+@@servername + 
' 
<br/>
Feedback updated
<br/>
From : ' + @from + '
<br/>
Date : ' + @date +'
<br/>
Subject : ' + @subject + 
'<br/>' + @feedback;

	declare @addresses varchar(2048)
	select @addresses = [dbo].[fn_get_admin_adresses]()

	EXEC msdb.dbo.sp_send_dbmail @recipients=@addresses,

    @subject = @subject1,

    @body = @body1,

    @body_format = 'HTML';

	END	
END

go